package lib.db.member;
/**
 * @author Tran Tuyen
 * @version 1.0
 */

import java.math.BigDecimal;
import java.sql.*;
import java.util.*;

import lib.db.pool.MySQLConnection;

import inet.util.*;
import lib.util.DateProc;
import lib.util.EncrypterStandard;

public class UserDAO {
    private Logger logger;

    private MySQLConnection dbPool = null;
	public UserDAO(){
		logger =new Logger(this.getClass().getName());
		try {
			dbPool = new MySQLConnection("private_db");
		} catch (Exception e) {
		}
	}
	
	@SuppressWarnings({ "unchecked", "finally" })
    public boolean insertRow(String username, String password, String fullname, int status) {
        if (username == null || password == null) {
            logger.log("insertRow: User or password is null");
            return false;
        }
        password = EncrypterStandard.md5Encrypt(password);

        Connection conn = null;
        PreparedStatement preStmt = null;
        String strSQL = null;
        
        Timestamp expiredDate = null;
        expiredDate = DateProc.TimestampPlusDay2Timestamp(DateProc.createTimestamp(), 30);
        
        boolean result = false;
        try {
            conn = dbPool.getConnection();
            
            strSQL =
                "INSERT INTO users (username, password, fullname, " +
                "gen_date, expired_date, status) " +
                "VALUES (?, ?, ?, now(), ?, ?)";
            preStmt = conn.prepareStatement(strSQL);
            preStmt.setString(1, username);
            preStmt.setString(2, password);
            preStmt.setString(3, fullname);
            preStmt.setTimestamp(4, expiredDate);
            preStmt.setInt(5, status);
            
            if (preStmt.executeUpdate() == 1) {
                result = true;
            }
        } catch (SQLException e) {
             logger.error("insertRow: Error executing SQL " + strSQL + ">>>" + e.toString());
        } finally {
            dbPool.releaseConnection(conn, preStmt);
            return result;
        }
    }

    @SuppressWarnings({ "unchecked", "finally" })
    public User getRow(BigDecimal userId) {
        Connection conn = null;
        PreparedStatement preStmt = null;
        ResultSet rs = null;

        String strSQL = null;
        User user = null;
        try {
            conn = dbPool.getConnection();
            strSQL =
                "SELECT username, password, fullname, " +
                "gen_date, status, expired_date FROM users " +
                "WHERE id = ?";
            preStmt = conn.prepareStatement(strSQL);
            preStmt.setBigDecimal(1, userId);
            rs = preStmt.executeQuery();
            if (rs.next()) {
            	user = new User();
            	user.setUsername(rs.getString(1));
            	user.setPassword(rs.getString(2));
            	user.setFullname(rs.getString(3));
            	user.setGenDate(rs.getTimestamp(4));
            	user.setStatus(rs.getInt(5));
            	user.setExpriedDate(rs.getTimestamp(6));
            	user.setId(userId);
            }
        } catch (SQLException e) {
            logger.error("getRow: Error executing SQL " + strSQL + ">>>" + e.toString());
        } finally {
            dbPool.releaseConnection(conn, preStmt, rs);
            return user;
        }
    }

    @SuppressWarnings({ "unchecked", "finally" })
    public boolean deleteRow(BigDecimal id) {
        boolean result = false;
        Connection conn = null;
        PreparedStatement preStmt = null;

        String strSQL = null;
        try {
           conn = dbPool.getConnection();
            strSQL =
                "DELETE FROM users WHERE id = ? ";
            preStmt = conn.prepareStatement(strSQL);
            preStmt.setBigDecimal(1, id);
            if (preStmt.executeUpdate() > 0) {
                result = true;
            }
        } catch(SQLException e) {
            logger.error("deleteRow: Error executing SQL " + strSQL + ">>>" + e.toString());
        } finally {
            dbPool.releaseConnection(conn, preStmt);
            return result;
        }
    }

    public int countAll() {
		Connection conn = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
		String strSQL = null;
		int result = 0;
		
		try {
			conn = dbPool.getConnection();
			strSQL = " SELECT COUNT(id) FROM users ";
			psmt = conn.prepareStatement(strSQL);
			rs = psmt.executeQuery();
			
			if (rs.next()) {
				result = rs.getInt(1);
			}
		} catch (Exception e) {
			logger.error("countAll: Error executing SQL " + strSQL + ">>>" + e.toString());
		} finally{
			dbPool.releaseConnection(conn, psmt, rs);
		}
		return result;
	}
    
    @SuppressWarnings({ "unchecked", "finally" })
    public Vector findAll(int currPage, int rowsPerPage) {
    	int startRows = (currPage - 1) * rowsPerPage;
        Connection conn = null;
        PreparedStatement preStmt = null;
        ResultSet rs = null;

        String strSQL = null;
        Vector result = null;
        User user = null;
        try {
            conn = dbPool.getConnection();
            strSQL =
                "SELECT id, username, a.password, fullname, " +
                "gen_date, expired_date, a.status FROM users a ORDER BY gen_date DESC, id DESC " +
                "LIMIT ? , ? ";
            preStmt = conn.prepareStatement(strSQL);
            preStmt.setInt(1, startRows);
            preStmt.setInt(2, rowsPerPage);
            rs = preStmt.executeQuery();

            result = new Vector();
            while (rs.next()) {
            	user = new User();
            	user.setId(rs.getBigDecimal(1));
            	user.setUsername(rs.getString(2));
            	user.setPassword(rs.getString(3));
            	user.setFullname(rs.getString(4));
            	user.setGenDate(rs.getTimestamp(5));
            	user.setExpriedDate(rs.getTimestamp(6));
            	user.setStatus(rs.getInt(7));
            	result.addElement(user);
            }
        } catch (SQLException e) {
            logger.error("findAll: Error executing SQL " + strSQL + ">>>" +e.toString());
        } finally {
            dbPool.releaseConnection(conn, preStmt, rs);
            return result;
        }
    }
    
    @SuppressWarnings({ "unchecked", "finally" })
    public User findByUsername(String username) {
        if (username == null) return null;

        Connection conn = null;
        PreparedStatement preStmt = null;
        ResultSet rs = null;

        String strSQL = null;
        User user = null;
        try {
            conn = dbPool.getConnection();
            strSQL =
                "SELECT id, password, fullname, " +
                "gen_date, status, expired_date " +
                "FROM users "  +
                "WHERE username = ?";
            preStmt = conn.prepareStatement(strSQL);
            preStmt.setString(1, username);
            rs = preStmt.executeQuery();
            if (rs.next()) {
            	user = new User();
            	user.setId(rs.getBigDecimal(1));
            	user.setUsername(rs.getString(2));
            	user.setPassword(rs.getString(3));
            	user.setFullname(rs.getString(4));
            	user.setGenDate(rs.getTimestamp(5));
            	user.setStatus(rs.getInt(6));
            	user.setExpriedDate(rs.getTimestamp(7));
            }

        } catch (SQLException e) {
            logger.error("findByUsername: Error executing SQL " + strSQL + ">>>" +e.toString());
        } finally {
            dbPool.releaseConnection(conn, preStmt, rs);
            return user;
        }
    }

    @SuppressWarnings({ "unchecked", "finally" })
    public boolean updateRow(BigDecimal id, String username, String password, String fullname, int status) {
        if (id == null) {
            logger.log("updateRow: Admin is null --> could not be updated");
            return false;
        }

        boolean result = false;
        Connection conn = null;
        PreparedStatement preStmt = null;
        String strSQL = null;
        try {
            conn = dbPool.getConnection();
            strSQL =
                "UPDATE users SET username = ?, password = ?, fullname = ?, status = ? " +
                "WHERE id = ?";
            preStmt = conn.prepareStatement(strSQL);
            preStmt.setString(1, username);
            preStmt.setString(2, password);
            preStmt.setString (3, fullname);
            preStmt.setInt(4, status);
            preStmt.setBigDecimal(5, id);
            
            if (preStmt.executeUpdate() == 1) {
                result = true;
            }
        } catch (SQLException e) {
            logger.error("updateRow: Error executing SQL " + strSQL + ">>>" + e.toString());
        } finally {
            dbPool.releaseConnection(conn, preStmt);
            return result;
        }
    }

    @SuppressWarnings({ "unchecked", "finally" })
    public boolean updatePassword(BigDecimal id, String newPassword) {
        if (id == null || newPassword == null) return false;
        //newPassword = EncrypterStandard.md5Encrypt(newPassword);

        boolean result = false;
        Connection conn = null;
        PreparedStatement preStmt = null;
        String strSQL = null;
        try {
            conn = dbPool.getConnection();
            strSQL =
                "UPDATE users SET password = ? WHERE id = ?";
            preStmt = conn.prepareStatement(strSQL);
            preStmt.setString(1, newPassword);
            preStmt.setBigDecimal(2, id);
            if (preStmt.executeUpdate() == 1) {
                result = true;
            }
        } catch (SQLException e) {
            logger.error("updatePassword: Error executing SQL " + strSQL + ">>>" + e.toString());
        } finally {
            dbPool.releaseConnection(conn, preStmt);
            return result;
        }
    }
    
    @SuppressWarnings({ "unchecked", "finally" })
    public boolean updateExpiredDate(BigDecimal id, Timestamp newExpiredDate) {
    	if (id == null || newExpiredDate == null) return false;
    	
    	boolean result = false;
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	String strSQL = null;
    	try {
    		conn = dbPool.getConnection();
    		strSQL =
    			"UPDATE users SET expired_date = ? WHERE id = ?";
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setTimestamp(1, newExpiredDate);
    		preStmt.setBigDecimal(2, id);
    		if (preStmt.executeUpdate() == 1) {
    			result = true;
    		}
    	} catch (SQLException e) {
    		logger.error("updateExpiredDate: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		dbPool.releaseConnection(conn, preStmt);
    		return result;
    	}
    }
    
    @SuppressWarnings({ "unchecked", "finally" })
    public boolean updateStatus(BigDecimal userId, int newStatus) {
        if (userId == null || newStatus < 0) return false;

        boolean result = false;
        Connection conn = null;
        PreparedStatement preStmt = null;
        String strSQL = null;
        try {
            conn = dbPool.getConnection();
            strSQL =
                "UPDATE users SET status = ? WHERE id = ?";
            preStmt = conn.prepareStatement(strSQL);
            preStmt.setInt(1, newStatus);
            preStmt.setBigDecimal(2, userId);
            if (preStmt.executeUpdate() == 1) {
                result = true;
            }
        } catch (SQLException e) {
            logger.error("updateStatus: Error executing SQL " + strSQL + ">>>" + e.toString());
        } finally {
            dbPool.releaseConnection(conn, preStmt);
            return result;
        }
    }
    
    @SuppressWarnings({ "unchecked" })
    public static void main(String[] args){
    	
    }
}
